package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import control.ConnectionManage;


import db.parking.beans.Orders;


public class AdminOrders {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();

	/*public static void displayAllRows() throws SQLException {

		String sql = "SELECT *  FROM Comlaints";
		try (
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				){

			System.out.println("Complaints Table:");
			while (rs.next()) {
				StringBuffer bf = new StringBuffer();
				bf.append(rs.getInt("id") + ": ");
				bf.append(rs.getString("username") +", ");
				bf.append(rs.getString("age"));
				System.out.println(bf.toString());
			}
		}
	}
*/
	public static Orders getRow(int oId) throws SQLException {

		String sql = "SELECT * FROM Orders WHERE oid = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, oId);
			rs = stmt.executeQuery();

			if (rs.next()) {
				Orders bean = new Orders();
				bean.setOid(oId);
				bean.setUid(rs.getString("uid"));
				bean.setType(rs.getString("type"));
				bean.setCarNo(rs.getString("carNo"));
				//bean.setLeaveTime(rs.TimeStamp.("currTime"));


				bean.setStatus(rs.getInt("status"));
				
				
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	public static boolean insert(Orders bean) throws Exception {
    //order id is autoIncremented on Insert

		String sql = "INSERT into Orders (uid , type , carNo , startTime, leaveTime , lotNo ,  email , status) " +
				"VALUES (?, ? ,? ,? ,? , ? , ? , ?)";
		ResultSet keys = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				) {
			
			//stmt.setInt(1, bean.getOid());
			stmt.setString(1, bean.getUid());
			stmt.setString(2, bean.getType());
			stmt.setString(3, bean.getCarNo());
			stmt.setTimestamp(4,bean.getStartTime());
			stmt.setTimestamp(5,bean.getLeaveTime());
			stmt.setInt(6, bean.getLotNo());
			stmt.setString(7, bean.getEmail());
			stmt.setInt(8, bean.getStatus());
			int affected = stmt.executeUpdate();
			
			if(affected == 1){
				keys = stmt.getGeneratedKeys();
				keys.next();
				int newKey = keys.getInt(1);
				bean.setOid(newKey);
			}
			else{
				System.err.println("No rows affected");
				return false;
			}
			
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally{
			if(keys != null)
				keys.close();
		}
		return true;
		
	}
	//update order status
	public static boolean update(Orders bean) throws Exception {

		String sql =
				"UPDATE Orders SET " +
				"status = ? " +
				"WHERE oid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getStatus());
			stmt.setInt(2,bean.getOid());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	//delete order by id
	public static boolean delete(int oId) throws Exception {

		String sql = "DELETE FROM Orders WHERE oid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,oId);
			int affected = stmt.executeUpdate();
			if(affected == 1){
					return true;
			} else {
				return false;
			}
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
}
